package com.swss.util;

import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.swss.g.assets.bean.Asset;
import com.swss.g.assets.bean.ChangeAsset;

public class Excel {
	/**
	 * 生成excel文件(文件标题栏与文件内容一定要对应)
	 * 
	 */
	@SuppressWarnings("unchecked") //在账
	public static void writeAssetExcel(List Datalist,int printType)
			throws IOException, RowsExceededException, WriteException {
		try {
			String title[] = null;// 标题数组
			title=getTitleName(printType);
			WritableWorkbook wwb = Workbook.createWorkbook(new File("C://数据Excel.xls")); // 创建可以写入的Excel工作薄(默认运行生成的文件在tomcat/bin下
			int pageNum = 0; // 多少页
			int pageSize = 50000;// 每页五万条数据
			pageNum = (Datalist.size() % pageSize > 0) ? (Datalist.size()/ pageSize + 1) : (Datalist.size() / pageSize);

			for (int i = 0; i < pageNum; i++) {
				// 创建excel区域名称
				WritableSheet sheet = wwb.createSheet("数据-" + i, i);
				int count = (i + 1) * pageSize > Datalist.size() ? Datalist.size() : (i + 1) * pageSize;
				for (int z = 0, j = i * pageSize; j < count; j++, z++) {
					Asset ast= (Asset) Datalist.get(j);// 获取一条(一行)记录
					for (int k = 0; k < title.length; k++) {
						Label label = new Label(k, 0, title[k].toString());
						sheet.addCell(label);
						//添加数据
						sheet.addCell(new Label(0,z+1,ast.getAstNo()));
						sheet.addCell(new Label(1,z+1,ast.getDept().getDeptName()));
						sheet.addCell(new Label(2,z+1,ast.getGbClassNo().toString()));
						sheet.addCell(new Label(3,z+1,ast.getAstName()));
						sheet.addCell(new Label(4,z+1,ast.getJlUnit()));
						sheet.addCell(new Label(5,z+1,ast.getUseYear()));
						sheet.addCell(new Label(6,z+1,ast.getBrand()));
						sheet.addCell(new Label(7,z+1,ast.getModelNo()));
						sheet.addCell(new Label(8,z+1,ast.getAstCount().toString()));
						sheet.addCell(new Label(9,z+1,ast.getPrice().toString()));
						sheet.addCell(new Label(10,z+1,ast.getAmount().toString()));
						sheet.addCell(new Label(11,z+1,ast.getFactoryName()));
						sheet.addCell(new Label(12,z+1,ast.getSuplierName()));
						sheet.addCell(new Label(13,z+1,ast.getBuyDate().toString()));
						sheet.addCell(new Label(14,z+1,ast.getRecordDate().toString()));
						sheet.addCell(new Label(15,z+1,ast.getCfddName()));
						sheet.addCell(new Label(16,z+1,ast.getCollarName()));
						sheet.addCell(new Label(17,z+1,ast.getHandName()));
						sheet.addCell(new Label(18,z+1,ast.getAccperName()));
						sheet.addCell(new Label(19,z+1,ast.getDanJuNo()));
						sheet.addCell(new Label(20,z+1,ast.getChargeOffDept().getTxt()));
						sheet.addCell(new Label(21,z+1,ast.getTelephoneNo()));
						sheet.addCell(new Label(22,z+1,ast.getNowStatus().getTxt()));
						sheet.addCell(new Label(23,z+1,ast.getFundSub().getTxt()));
						sheet.addCell(new Label(24,z+1,ast.getUseDir().getTxt()));
						sheet.addCell(new Label(25,z+1,ast.getAstSource().getTxt()));
						sheet.addCell(new Label(26,z+1,ast.getAstClassName().getTxt()));
						sheet.addCell(new Label(27,z+1,ast.getFinVoucher()));
						sheet.addCell(new Label(28,z+1,ast.getFinAdtDate().toString()));
						sheet.addCell(new Label(29,z+1,ast.getFinAdtName()));
						sheet.addCell(new Label(30,z+1,ast.getFinAccountName().getTxt()));
						sheet.addCell(new Label(31,z+1,ast.getFinAdvice()));
						sheet.addCell(new Label(32,z+1,ast.getRemarks()));
					}
				}
			}
			// 写入数据
			wwb.write();
			// 关闭文件
			wwb.close();
			System.out.println("导出EXCEL成功!");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	public static void writeChangeAssetExcel(List<Object> Datalist,int printType)
			throws IOException, RowsExceededException, WriteException {
		try {
			String title[] = null;// 标题数组
			title=getTitleName(printType);
			WritableWorkbook wwb = Workbook.createWorkbook(new File("C://数据Excel.xls")); // 创建可以写入的Excel工作薄(默认运行生成的文件在tomcat/bin下
			int pageNum = 0; // 多少页
			int pageSize = 3;// 每页五万条数据
			pageNum = (Datalist.size() % pageSize > 0) ? (Datalist.size()/ pageSize + 1) : (Datalist.size() / pageSize);

			for (int i = 0; i < pageNum; i++) {
				// 创建excel区域名称
				WritableSheet sheet = wwb.createSheet("数据-" + i, i);
				int count = (i + 1) * pageSize > Datalist.size() ? Datalist.size() : (i + 1) * pageSize;
				for (int z = 0, j = i * pageSize; j < count; j++, z++) {
					ChangeAsset chageast= (ChangeAsset) Datalist.get(j);// 获取一条(一行)记录
					for (int k = 0; k < title.length; k++) {
						Label label = new Label(k, 0, title[k].toString());
						sheet.addCell(label);
						//添加变动数据
						sheet.addCell(new Label(0,z+1,chageast.getAstNo()));
						sheet.addCell(new Label(1,z+1,chageast.getDept().getDeptName()));
						
						sheet.addCell(new Label(2,z+1,chageast.getAstName()));
					}
				}
			}
			// 写入数据
			wwb.write();
			// 关闭文件
			wwb.close();
			System.out.println("导出EXCEL成功!");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 设置EXCEL文件标题
	 * @param type
	 * @return
	 */
	public static String[] getTitleName(int type) {
		String title[] = null;// 标题数组
		if (type == 1) {
			title = new String[] { "设备编号", "单位名称", "国标分类号", "设备名称", "计量单位",
					"使用年限", "品牌", "规格", "数量", "单价", "金额", "厂家", "供货商", "购置日期",
					"入账时间", "存放地点", "领用人", "经手人", "记账人", "单据号", "拟核销科室",
					"联系电话", "现状", "经费来源", "使用方向", "设备来源", "资产类别", "财务凭单",
					"财审日期", "财审人", "财务账户", "财务意见", "备注" };
		} else {
			title = new String[] { "设备编号", "单位名称", "国标分类号", "设备名称", "计量单位",
					"使用年限", "品牌", "规格", "数量", "单价", "金额", "厂家", "供货商", "购置日期",
					"入账时间", "存放地点", "领用人", "经手人", "记账人", "单据号", "拟核销科室",
					"联系电话", "现状", "经费来源", "使用方向", "设备来源", "资产类别", "财务凭单",
					"财审日期", "财审人", "财务账户", "财务意见", "备注" };
		}
		return title;
	}
}
